# -*- coding: utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf8')

from services.weixin import *
from config import DBWeixinConfig, NoticeConfig as notice
import random
import datetime

dbManager = DBManager(DBWeixinConfig)

class WeixinService():

	def addText(self,txtDict):
		sql = 'insert into wx_text(`%s`) values ("%s")' % ('`,`'.join(txtDict.keys()),'","'.join(txtDict.values()))
		dbManager.executeNonQuery(sql)

	def addImage(self,imageDict):
		sql = 'insert into wx_image(`%s`) values ("%s")' % ('`,`'.join(imageDict.keys()),'","'.join(imageDict.values()))
		dbManager.executeNonQuery(sql)

	def addVoice(self,voiceDict):
		sql = 'insert into wx_voice(`%s`) values ("%s")' % ('`,`'.join(voiceDict.keys()),'","'.join(voiceDict.values()))
		dbManager.executeNonQuery(sql)

	def addVideo(self,videoDict):
		sql = 'insert into wx_video(`%s`) values ("%s")' % ('`,`'.join(videoDict.keys()),'","'.join(videoDict.values()))
		dbManager.executeNonQuery(sql)

	def addLocation(self,locDict):
		sql = 'insert into wx_location(`%s`) values ("%s")' % ('`,`'.join(locDict.keys()),'","'.join(locDict.values()))
		dbManager.executeNonQuery(sql)

	def addMsgLocation(self,msgLocDict):
		sql = 'insert into wx_msg_location(`%s`) values ("%s")' % ('`,`'.join(msgLocDict.keys()),'","'.join(msgLocDict.values()))
		dbManager.executeNonQuery(sql)

	def addLink(self,linkDict):
		sql = 'insert into wx_link(`%s`) values ("%s")' % ('`,`'.join(linkDict.keys()),'","'.join(linkDict.values()))
		dbManager.executeNonQuery(sql)

	def addClickEvent(self,eventDict):
		#logger.debug('click')
		sql = 'insert into wx_click_event(`%s`) values ("%s")' % ('`,`'.join(eventDict.keys()),'","'.join(eventDict.values()))
		#logger.debug(sql)
		dbManager.executeNonQuery(sql)

	def addSubscribeEvent(self,eventDict):
		#logger.debug('Subscribe')
		sql = 'insert into wx_subscribe_event(`%s`) values ("%s")' % ('`,`'.join(eventDict.keys()),'","'.join(eventDict.values()))
		dbManager.executeNonQuery(sql)

	def addUnsubscribeEvent(self,eventDict):
		#logger.debug('Unsubscribe')
		sql = 'insert into wx_unsubscribe_event(`%s`) values ("%s")' % ('`,`'.join(eventDict.keys()),'","'.join(eventDict.values()))
		dbManager.executeNonQuery(sql)

	def addScanEvent(self,eventDict):
		#logger.debug('Scan')
		sql = 'insert into wx_scan_event(`%s`) values ("%s")' % ('`,`'.join(eventDict.keys()),'","'.join(eventDict.values()))
		dbManager.executeNonQuery(sql)

	def addLocationEvent(self,eventDict):
		#logger.debug('Location')
		sql = 'insert into wx_location_event(`%s`) values ("%s")' % ('`,`'.join(eventDict.keys()),'","'.join(eventDict.values()))
		dbManager.executeNonQuery(sql)

	def getResponseData(self,eventKey):
		sql = 'SELECT `Id`,`ParentId`,`EventName`,`EventKey`,`MsgType`,`Content`,`MediaId`,`ThumbMediaId`,`Title`,`Description`,'+\
		'		`MusicUrl`,`HQMusicUrl`,`PicUrl`,`Url` FROM msg_response WHERE `EventKey` = "%s";' % (eventKey)
		result = dbManager.executeQuery(sql)
		return result

	'''
	ZT add
	'''

	def addUser(self,userDict):
		#logger.debug('AddUser')

		openid = userDict['openid']
		sql = 'select id from user_info where openid = "%s"' % openid
		result = dbManager.executeQuery(sql)
		if len(result) > 0:
			
			del(userDict['openid'])

			s = []
			for (k, v) in userDict.items():
				val = v
				if type(v) != int:
					val = '"'+v+'"'
				s.append("`%s` = %s " % (k, val))

			sql = 'update user_info set status = 1 , '+','.join(s)+' where openid = "%s"' % openid  #+sql_set+' where openid = %s' % tuple(values)
		else:
			list_key_int = []
			list_value_int = []
			list_key_str = []
			list_value_str = []

			for (k, v) in userDict.items():
				val = v
				if type(v) == int:
					list_key_int.append(k)
					list_value_int.append(str(v))
				else:
					list_key_str.append(k)
					list_value_str.append(dbManager.escape_string(v))

			sql = 'insert into user_info(`%s`) values ("%s)' % ('`,`'.join(list_key_str)+'`,`'+'`,`'.join(list_key_int),'","'.join(list_value_str)+'",'+','.join(list_value_int))

		dbManager.executeNonQuery(sql)

	def delUser(self,openid):
		#logger.debug('DelUser')
		user = self.__getUserByOpenid(openid)
		master = self.getUserMaster(openid)

		sql = 'update user_info set subscribe = 0, status = 0 where openid = "%s"' % openid

		dbManager.executeNonQuery(sql)

		if master is not None: # 存在主人，主人资产+奴隶身价 
			sqls = self.__earnAsset(master['id'],user['price'],0)
			dbManager.executeNonQueries(sqls)

	def getSlavers(self,openid,pageindex,pagesize):
		#logger.debug('getUsers')
		pageindex = 0 if (pageindex - 1)*pagesize < 0 else (pageindex - 1)*pagesize

		sql = '''SELECT ui.id, ui.nickname, ui.price, ui.`status`
		FROM user_info AS ui
		WHERE subscribe = 1 
		AND openid != "%s"
		AND NOT EXISTS 
		(
		SELECT 1
		FROM user_relationship AS ur
		INNER JOIN user_info AS slaver_u ON ur.slaver_uid = slaver_u.id AND ur.statusid = 1 AND slaver_u.subscribe = 1
		INNER JOIN user_info AS master_u ON ur.master_uid = master_u.id AND master_u.subscribe = 1 
		AND master_u.openid = "%s"
		WHERE slaver_u.id = ui.id
		)
		ORDER BY `status` DESC, op_time DESC LIMIT %d, %d
		''' % (openid,openid,pageindex,pagesize)

		users = dbManager.executeQuery(sql)

		return users

	def getUserInfo(self,fromuser):
		user = self.__getUserByOpenid(fromuser)
		return user

	def getUserInfoById(self,userid):
		user = self.__getUserByUid(int(userid))
		return user

	def getUserSlavers(self,fromuser,pageindex,pagesize):

		pageindex = 0 if (pageindex - 1)*pagesize < 0 else (pageindex - 1)*pagesize

		sql = '''SELECT slaver_u.id, slaver_u.nickname, slaver_u.openid, slaver_u.price, slaver_u.asset, slaver_u.subscribe
		FROM user_relationship AS ur
		INNER JOIN user_info AS slaver_u ON ur.slaver_uid = slaver_u.id AND ur.statusid = 1 AND slaver_u.subscribe = 1
		INNER JOIN user_info AS master_u ON ur.master_uid = master_u.id AND master_u.subscribe = 1 AND master_u.openid = "%s" 
		ORDER BY ur.createdtime DESC LIMIT %d, %d ''' % (fromuser,pageindex,pagesize)

		slavers = dbManager.executeQuery(sql)

		return slavers

	def getUserSlaversByName(self,openid,username,pageindex = 1,pagesize = 10):

		pageindex = 0 if (pageindex - 1)*pagesize < 0 else (pageindex - 1)*pagesize

		sql = '''SELECT ui.id, ui.nickname, ui.price, ui.`status`
		FROM user_info AS ui
		WHERE lower(nickname) like "%%%s%%"
		AND subscribe = 1 
		AND openid != "%s"
		AND NOT EXISTS 
		(
		SELECT 1
		FROM user_relationship AS ur
		INNER JOIN user_info AS slaver_u ON ur.slaver_uid = slaver_u.id AND ur.statusid = 1 AND slaver_u.subscribe = 1
		INNER JOIN user_info AS master_u ON ur.master_uid = master_u.id AND master_u.subscribe = 1 
		AND master_u.openid = "%s"
		WHERE slaver_u.id = ui.id
		)
		ORDER BY `status` DESC, op_time DESC LIMIT %d, %d
		''' % (username.lower(),openid,openid,pageindex,pagesize)

		#logger.debug(sql)

		userinfo = dbManager.executeQuery(sql)

		return userinfo

	def getUserSlaversCount(self,fromuser):
		sql = '''SELECT count(distinct ur.slaver_uid) as slaverscnt
		FROM user_relationship AS ur
		INNER JOIN user_info AS slaver_u ON ur.slaver_uid = slaver_u.id AND ur.statusid = 1 AND slaver_u.subscribe = 1
		INNER JOIN user_info AS master_u ON ur.master_uid = master_u.id AND master_u.subscribe = 1 AND master_u.openid = "%s" 
		''' % (fromuser)

		slavers = dbManager.executeQuery(sql)

		return slavers[0]['slaverscnt']

	def getRandomUserSlaver(self,fromuser):

		sql = '''SELECT slaver_u.id, slaver_u.nickname, slaver_u.openid, slaver_u.price, slaver_u.asset, slaver_u.subscribe
		FROM user_relationship AS ur
		INNER JOIN user_info AS slaver_u ON ur.slaver_uid = slaver_u.id AND ur.statusid = 1 AND slaver_u.subscribe = 1
		INNER JOIN user_info AS master_u ON ur.master_uid = master_u.id AND master_u.subscribe = 1 AND master_u.openid = "%s" 
		ORDER BY ur.createdtime DESC''' % (fromuser)

		slavers = dbManager.executeQuery(sql)

		if len(slavers) == 0:
			return None
		return random.choice(slavers)

	def getUserSlaverById(self,fromuser,slaverid):

		sql = '''SELECT slaver_u.id, slaver_u.nickname, slaver_u.openid, slaver_u.price, slaver_u.asset, slaver_u.subscribe
		FROM user_relationship AS ur
		INNER JOIN user_info AS slaver_u ON ur.slaver_uid = slaver_u.id AND ur.statusid = 1 AND slaver_u.subscribe = 1
		INNER JOIN user_info AS master_u ON ur.master_uid = master_u.id AND master_u.subscribe = 1 AND master_u.openid = "%s" 
		WHERE slaver_u.id = %d
		ORDER BY ur.createdtime DESC''' % (fromuser,int(slaverid))

		slaver = dbManager.executeQuery(sql)

		if len(slaver) == 0:
			return None
		return slaver[0]

	def getUserMaster(self,fromuser):

		sql = '''SELECT master_u.id, master_u.nickname, master_u.openid, master_u.price, master_u.asset, master_u.subscribe
		FROM user_relationship AS ur
		INNER JOIN user_info AS slaver_u ON ur.slaver_uid = slaver_u.id AND slaver_u.subscribe = 1 AND slaver_u.openid = "%s"
		INNER JOIN user_info AS master_u ON ur.master_uid = master_u.id AND ur.statusid = 1 AND master_u.subscribe = 1
		ORDER BY ur.createdtime DESC ''' % fromuser

		masterinfo = dbManager.executeQuery(sql)

		if len(masterinfo) > 0:
			return masterinfo[0]
		else:
			return None

	def getUserPriceRank(self,userid):
		sql = '''SELECT id,rank
		FROM
		(SELECT a.*,@rank := @rank + 1 AS rank
			FROM
			(SELECT id
				FROM user_info
				ORDER BY price DESC) a,
			(SELECT @rank := 0) b
		) AS t
		WHERE id = %d''' % int(userid)

		sql_new = '''SELECT u.id,t.rank
		FROM
		(SELECT a.*,@rank := @rank + 1 AS rank
			FROM
			(SELECT DISTINCT price
				FROM user_info
				ORDER BY price DESC) a,
			(SELECT @rank := 0) b
		) AS t
		INNER JOIN user_info AS u ON u.price = t.price
		WHERE id = %d''' % int(userid)

		rank = dbManager.executeQuery(sql_new)

		return rank[0]['rank']

	def getUserAssetRank(self,userid):
		sql = '''SELECT id,rank
		FROM
		(SELECT a.*,@rank := @rank + 1 AS rank
			FROM
			(SELECT id
				FROM user_info
				ORDER BY asset DESC) a,
			(SELECT @rank := 0) b
		) AS t
		WHERE id = %d''' % int(userid)

		sql_new = '''SELECT u.id,t.rank
		FROM
		(SELECT a.*,@rank := @rank + 1 AS rank
			FROM
			(SELECT DISTINCT asset
				FROM user_info
				ORDER BY asset DESC) a,
			(SELECT @rank := 0) b
		) AS t
		INNER JOIN user_info AS u ON u.asset = t.asset
		WHERE id = %d''' % int(userid)

		rank = dbManager.executeQuery(sql_new)

		return rank[0]['rank']

	def redeem(self,masterid,userid,price):

		sqls = []
		sqls = sqls + self.__payAsset(int(userid),price,6)
		sqls = sqls + self.__loseSlaver(masterid,int(userid))
		sqls = sqls + self.__earnAsset(masterid,int(userid),7)

		dbManager.executeNonQueries(sqls)

	def getRandomActivity(self,typeid):
		sql = 'SELECT `id`,activities,master_msg,slaver_msg,effect FROM slaver_activities WHERE typeid = %d' % typeid
		activities = dbManager.executeQuery(sql)

		activity = random.choice(activities)

		return activity

	def setUserStatus(self,fromuser,status):
		sql = 'update user_info set status = %d, price = price*%d/100  where openid = "%s"' % (status,random.randint(80,90),fromuser)

		dbManager.executeNonQuery(sql)

	def escape(self, slaverid, userid, asset):
		#logger.debug('escape')
		sqls = []
		sqls = sqls + self.__loseSlaver(int(userid),int(slaverid))
		if asset > 0:
			sqls = sqls + self.__payAsset(int(userid),asset,8)

		dbManager.executeNonQueries(sqls)

	def reward(self, masterid, userid, asset):
		#logger.debug('reward')
		sqls = []
		sqls = sqls + self.__payAsset(int(masterid),asset,10)
		sqls = sqls + self.__earnAsset(int(userid),asset,5)

		dbManager.executeNonQueries(sqls)

	def getRelationship(self,fromuserid,userid):
		sql = '''SELECT '1' AS relationship
		FROM user_relationship
		WHERE master_uid = %d AND slaver_uid = %d AND statusid = 1
		UNION
		SELECT '2' AS relationship
		FROM user_relationship
		WHERE master_uid = %d AND slaver_uid = %d AND statusid = 1
		''' % (fromuserid,int(userid),int(userid),fromuserid)

		relationship = dbManager.executeQuery(sql)

		if len(relationship) > 0:
			return int(relationship[0]['relationship'])
		else:
			return 0

	def buySlave(self,fromuserid,slaverid,price,masterid = None):

		rand = random.randint(1,10)

		sqls = []
		sqls = sqls + self.__payAsset(fromuserid,price,4)
		if masterid is not None: # 如果存在主人，则主人需失去奴隶，主人资产+奴隶身价
			sqls = sqls + self.__loseSlaver(int(masterid),int(slaverid))
			sqls = sqls + self.__earnAsset(int(masterid),price,3)			
		else: # 如果没有主人，则奴隶自己资产+奴隶身价
			sqls = sqls + self.__earnAsset(int(slaverid),price,3)
		sqls = sqls + self.__getSlaver(fromuserid,int(slaverid),rand)

		dbManager.executeNonQueries(sqls)

		return int(price+price*rand/100)

	def freeSlaver(self,fromuserid,slaverid,asset):
		sqls = []
		sqls = sqls + self.__loseSlaver(fromuserid,int(slaverid))
		sqls = sqls + self.__earnAsset(fromuserid,asset,9)

		dbManager.executeNonQueries(sqls)

		return asset

	def signIn(self, fromuser):

		user = self.__getUserByOpenid(fromuser)
		dt = datetime.datetime.now().strftime('%Y%m%d')
		d = {}

		sql = 'SELECT uid,daykey,asset FROM user_daily_action WHERE uid = %d AND daykey = %s AND `type` = 1' % (user['id'],dt)

		checkin = dbManager.executeQuery(sql)
		if len(checkin) == 0:
			d['isSignIn'] = False
			signInMoney = random.randint(10,100) 

			self.__dailyAction(user['id'],dt,signInMoney,1)
			d['signInMoney'] = signInMoney

		else:
			d['isSignIn'] = True
			d['signInMoney'] = checkin[0]['asset']

		return d

	def bindUser(self, fromuser, email, deviceid):

		sql = '''SELECT username 
		FROM et_go_db_prod.userbasic 
		WHERE loginemail = "%s" ''' % email

		bind = dbManager.executeQuery(sql)

		if len(bind) == 0:
			return 1

		sql = 'SELECT etid FROM user_info WHERE openid = "%s"' % fromuser

		bind = dbManager.executeQuery(sql)

		if int(bind[0]['etid']) > 0:
			return 3

		sql = '''SELECT ub.uid,ub.username
		FROM et_go_db_prod.userbasic AS ub
		INNER JOIN et_go_db_prod.deviceinfo AS de ON ub.uid = de.uid 
		WHERE ub.loginemail = "%s" AND lower(deviceid) = "%s"''' % (email, deviceid.lower())

		bind = dbManager.executeQuery(sql)

		if len(bind) == 0:
			return 2

		etid = bind[0]['uid']
		sql = 'select `id` from user_info where etid = %d' % etid
		result = dbManager.executeQuery(sql)

		if len(result) > 0:
				return 4
		sql = 'update user_info set etid = %d where openid = "%s"' % (etid,fromuser)
		dbManager.executeNonQuery(sql)
		return 0

	def cancelBind(self,fromuser):

		#logger.debug('cancelbind')

		sql = 'update user_info set etid = 0 where openid = "%s"' % (fromuser)

		dbManager.executeNonQuery(sql)

	def getBindStatus(self,fromuser):

		user = self.__getUserByOpenid(fromuser)

		if user['etid'] > 0:
			return 1
		else:
			return 2

	def syncETData(self,openid):

		user = self.__getUserByOpenid(openid)
		dt = datetime.datetime.now().strftime('%Y%m%d')

		sql = 'SELECT uid,daykey,asset FROM user_daily_action WHERE uid = %d AND daykey = %s AND `type` = 2' % (user['id'],dt)

		checkin = dbManager.executeQuery(sql)
		if len(checkin) == 0:
			sql = 'SELECT uid,daykey,asset FROM user_daily_action WHERE etid = %d AND daykey = %s AND `type` = 2' % (user['etid'],dt)
			sync = dbManager.executeQuery(sql)

			if len(sync) == 0:
				syncMoney = self.__getETCalorie(user['etid'])
				self.__dailyAction(user['id'],dt,syncMoney,2,user['etid'])
			else:
				syncMoney = 0

		else:
			syncMoney = checkin[0]['asset']

		return syncMoney

	def batchUpdateUsers(self,args):
		#logger.debug('batch update users')

		keys = args[0].keys()
		keys.remove('openid')

		sql_set = '` = value,`'.join(keys)
		sql_set = '`'+sql_set+'` = value '
		sql_set = sql_set.replace('value','%s')

		sql = " UPDATE user_info SET " \
		+ sql_set + \
		" WHERE openid = %s"

		values = []
		for arg in args:

			value = arg.values()
			value.remove(arg['openid'])
			value.append(arg['openid'])
			values.append(tuple(value))

		result = dbManager.executeMany(sql, values)
		'''
		sql = ""
		sqlArgs = []
		for user in users:
			openIdDict = user.pop("openid")
			if(sql == ""){ # 首次 构建sql语句
				setItems = []
				for (k,v) in user.items():
					vType = "str"
					if type(v) != int:
						vType = "int"
					setItems.append(" `%s` = [%sValue]" % (k,vType))

				setSql = ','.join(setItems).replace('[strValue]','"%s"').replace('[intValue]','%d')
				sql = 'UPDATE user_info SET ' + setSql + ' WHERE openid = "%s"'
			}
			user.append(openIdDict) #将openId 添加到user对象的最后面
			sqlArgs.append(user.values())

		dbManager.executeMany(sql, sqlArgs)
		'''

	def batchInsertUsers(self,args):
		#logger.debug('batch insert users')
		sql = " insert into user_info(" + \
		'`' + '`,`'.join(args[0].keys()) + '`' + \
		") values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) "
	
		values = []
		for arg in args:
			values.append(tuple(arg.values()))

		result = dbManager.executeMany(sql, values)


	def __dailyAction(self,userid,daykey,asset,typeid,etid = 0):
		sqls = []
		sql = 'INSERT INTO user_daily_action(uid,`type`,daykey,asset,etid) VALUES(%d,%d,%s,%d,%d)' % (userid,typeid,daykey,asset,etid)

		sqls.append(sql)
		sqls = sqls + self.__earnAsset(userid,asset,11)

		dbManager.executeNonQueries(sqls)

	def __getETCalorie(self,etid):

		if etid == 0:
			return 0
		else:
			dt = datetime.datetime.now()
			a = datetime.timedelta(hours=10)
			dt = (dt - a).strftime('%Y%m%d')

			sql = 'SELECT calorie FROM et_go_db_prod.v_user_data WHERE uid = %d AND daykey = %s' % (etid, dt)
			calories = dbManager.executeQuery(sql)
			if len(calories) > 0:
				return int(calories[0]['calorie'])
			else:
				return 0

	def __getUserByOpenid(self,openid):
		#logger.debug('SELECTUser')
		sql = '''select 
				id, 
				openid, 
				nickname, 
				subscribe, 
				price,
				asset,
				etid,
				status
				from user_info where openid = "%s"''' % openid

		userinfo = dbManager.executeQuery(sql)

		return userinfo[0]

	def __getUserByUid(self,uid):
		#logger.debug('SELECTUser')
		sql = '''select 
				id, 
				openid, 
				nickname, 
				subscribe, 
				price,
				asset,
				etid,
				status
				from user_info where id = %d''' % uid

		userinfo = dbManager.executeQuery(sql)

		return userinfo[0]

	def __payAsset(self,uid,asset,asset_type = 2):
		#logger.debug('payvalue')
		sqls = []
		sql = 'insert into user_asset_info(`uid`,`asset`,`asset_type`) values(%d,%d,%d)' % (uid,0-asset,asset_type)
		sqls.append(sql)

		sql = 'update user_info set asset = asset - %d where id = %d' % (asset,uid)
		sqls.append(sql)

		return sqls

	def __earnAsset(self,uid,asset,asset_type = 1):
		#logger.debug('earnvalue')
		sqls = []
		sql = 'insert into user_asset_info(`uid`,`asset`,`asset_type`) values(%d,%d,%d)' % (uid,asset,asset_type)
		sqls.append(sql)
		sql = 'update user_info set asset = asset + %d where id = %d' % (asset,uid)
		sqls.append(sql)

		return sqls

	def __getSlaver(self,masterid,slaverid,rand,action_type = 1):
		#logger.debug('buyslaver')
		sqls = []
		sql = 'insert into user_action_info(`master_uid`,`slaver_uid`,`action_type`) values(%d,%d,%d)' % (masterid,slaverid,action_type)		
		sqls.append(sql)
		sql = 'insert into user_relationship(`master_uid`,`slaver_uid`,`statusid`) values(%d,%d,%d)' % (masterid,slaverid,1)
		sqls.append(sql)
		sql = 'update user_info set price = price + price*%d/100, status = 1 where id = %d' % (rand,slaverid)
		sqls.append(sql)
		
		return sqls

	def __loseSlaver(self,masterid,slaverid,action_type = 2):
		#logger.debug('loseslaver')
		sqls = []
		sql = 'insert into user_action_info(`master_uid`,`slaver_uid`,`action_type`) values(%d,%d,%d)' % (masterid,slaverid,action_type)
		sqls.append(sql)
		sql = 'update user_relationship set statusid = 2 where master_uid = %d and slaver_uid = %s' % (masterid,slaverid)
		sqls.append(sql)

		return sqls

	